﻿using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Xml;
using System.Data.Common;

namespace Company.BLL.SXXK.ThanhKhoan
{
    public partial class LanThanhLyBase : EntityBase
    {
        #region Private members.

        protected int _LanThanhLy;
        protected string _MaDoanhNghiep = String.Empty;
        protected string _MaHaiQuan = String.Empty;
        protected int _SoHoSo;
        protected short _NamThanhLy;
        protected DateTime _NgayBatDau = new DateTime(1900, 01, 01);
        protected DateTime _NgayKetThuc = new DateTime(1900, 01, 01);
        protected string _SoQuyetDinh = String.Empty;
        protected DateTime _NgayQuyetDinh = new DateTime(1900, 01, 01);
        protected int _TrangThai;
        protected string _KieuThanhLy = String.Empty;

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Properties.

        public int LanThanhLy
        {
            set { this._LanThanhLy = value; }
            get { return this._LanThanhLy; }
        }
        public string MaDoanhNghiep
        {
            set { this._MaDoanhNghiep = value; }
            get { return this._MaDoanhNghiep; }
        }
        public string MaHaiQuan
        {
            set { this._MaHaiQuan = value; }
            get { return this._MaHaiQuan; }
        }
        public int SoHoSo
        {
            set { this._SoHoSo = value; }
            get { return this._SoHoSo; }
        }
        public short NamThanhLy
        {
            set { this._NamThanhLy = value; }
            get { return this._NamThanhLy; }
        }
        public DateTime NgayBatDau
        {
            set { this._NgayBatDau = value; }
            get { return this._NgayBatDau; }
        }
        public DateTime NgayKetThuc
        {
            set { this._NgayKetThuc = value; }
            get { return this._NgayKetThuc; }
        }
        public string SoQuyetDinh
        {
            set { this._SoQuyetDinh = value; }
            get { return this._SoQuyetDinh; }
        }
        public DateTime NgayQuyetDinh
        {
            set { this._NgayQuyetDinh = value; }
            get { return this._NgayQuyetDinh; }
        }
        public int TrangThai
        {
            set { this._TrangThai = value; }
            get { return this._TrangThai; }
        }
        public string KieuThanhLy
        {
            set { this._KieuThanhLy = value; }
            get { return this._KieuThanhLy; }
        }

        //---------------------------------------------------------------------------------------------

        public bool IsExist
        {
            get
            {
                return this.Load();
            }
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Select methods.

        public bool Load()
        {
            string spName = "p_SXXK_ThanhLy_LanThanhLy_Load";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);

            IDataReader reader = this.db.ExecuteReader(dbCommand);
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("LanThanhLy"))) this._LanThanhLy = reader.GetInt32(reader.GetOrdinal("LanThanhLy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) this._MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) this._MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoHoSo"))) this._SoHoSo = reader.GetInt32(reader.GetOrdinal("SoHoSo"));
                if (!reader.IsDBNull(reader.GetOrdinal("NamThanhLy"))) this._NamThanhLy = reader.GetInt16(reader.GetOrdinal("NamThanhLy"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayBatDau"))) this._NgayBatDau = reader.GetDateTime(reader.GetOrdinal("NgayBatDau"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKetThuc"))) this._NgayKetThuc = reader.GetDateTime(reader.GetOrdinal("NgayKetThuc"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoQuyetDinh"))) this._SoQuyetDinh = reader.GetString(reader.GetOrdinal("SoQuyetDinh"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayQuyetDinh"))) this._NgayQuyetDinh = reader.GetDateTime(reader.GetOrdinal("NgayQuyetDinh"));
                if (!reader.IsDBNull(reader.GetOrdinal("TrangThai"))) this._TrangThai = reader.GetInt32(reader.GetOrdinal("TrangThai"));
                if (!reader.IsDBNull(reader.GetOrdinal("KieuThanhLy"))) this._KieuThanhLy = reader.GetString(reader.GetOrdinal("KieuThanhLy"));
                return true;
            }
            return false;
        }

        //---------------------------------------------------------------------------------------------



        public DataSet SelectAll()
        {
            string spName = "p_SXXK_ThanhLy_LanThanhLy_SelectAll";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);
            return this.db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public IDataReader SelectReaderAll()
        {
            string spName = "p_SXXK_ThanhLy_LanThanhLy_SelectAll";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);
            return this.db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public DataSet SelectDynamic(string whereCondition, string orderByExpression)
        {
            string spName = "p_SXXK_ThanhLy_LanThanhLy_SelectDynamic";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            this.db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return this.db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public IDataReader SelectReaderDynamic(string whereCondition, string orderByExpression)
        {
            string spName = "p_SXXK_ThanhLy_LanThanhLy_SelectDynamic";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            this.db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return this.db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public LanThanhLyCollection SelectCollectionAll()
        {
            LanThanhLyCollection collection = new LanThanhLyCollection();

            IDataReader reader = this.SelectReaderAll();
            while (reader.Read())
            {
                LanThanhLyBase entity = new LanThanhLyBase();

                if (!reader.IsDBNull(reader.GetOrdinal("LanThanhLy"))) entity.LanThanhLy = reader.GetInt32(reader.GetOrdinal("LanThanhLy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) entity.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) entity.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoHoSo"))) entity.SoHoSo = reader.GetInt32(reader.GetOrdinal("SoHoSo"));
                if (!reader.IsDBNull(reader.GetOrdinal("NamThanhLy"))) entity.NamThanhLy = reader.GetInt16(reader.GetOrdinal("NamThanhLy"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayBatDau"))) entity.NgayBatDau = reader.GetDateTime(reader.GetOrdinal("NgayBatDau"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKetThuc"))) entity.NgayKetThuc = reader.GetDateTime(reader.GetOrdinal("NgayKetThuc"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoQuyetDinh"))) entity.SoQuyetDinh = reader.GetString(reader.GetOrdinal("SoQuyetDinh"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayQuyetDinh"))) entity.NgayQuyetDinh = reader.GetDateTime(reader.GetOrdinal("NgayQuyetDinh"));
                if (!reader.IsDBNull(reader.GetOrdinal("TrangThai"))) entity.TrangThai = reader.GetInt32(reader.GetOrdinal("TrangThai"));
                if (!reader.IsDBNull(reader.GetOrdinal("KieuThanhLy"))) entity.KieuThanhLy = reader.GetString(reader.GetOrdinal("KieuThanhLy"));
                collection.Add(entity);
            }
            return collection;
        }

        //---------------------------------------------------------------------------------------------

        public LanThanhLyCollection SelectCollectionDynamic(string whereCondition, string orderByExpression)
        {
            LanThanhLyCollection collection = new LanThanhLyCollection();

            IDataReader reader = this.SelectReaderDynamic(whereCondition, orderByExpression);
            while (reader.Read())
            {
                LanThanhLyBase entity = new LanThanhLyBase();

                if (!reader.IsDBNull(reader.GetOrdinal("LanThanhLy"))) entity.LanThanhLy = reader.GetInt32(reader.GetOrdinal("LanThanhLy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) entity.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) entity.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoHoSo"))) entity.SoHoSo = reader.GetInt32(reader.GetOrdinal("SoHoSo"));
                if (!reader.IsDBNull(reader.GetOrdinal("NamThanhLy"))) entity.NamThanhLy = reader.GetInt16(reader.GetOrdinal("NamThanhLy"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayBatDau"))) entity.NgayBatDau = reader.GetDateTime(reader.GetOrdinal("NgayBatDau"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKetThuc"))) entity.NgayKetThuc = reader.GetDateTime(reader.GetOrdinal("NgayKetThuc"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoQuyetDinh"))) entity.SoQuyetDinh = reader.GetString(reader.GetOrdinal("SoQuyetDinh"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayQuyetDinh"))) entity.NgayQuyetDinh = reader.GetDateTime(reader.GetOrdinal("NgayQuyetDinh"));
                if (!reader.IsDBNull(reader.GetOrdinal("TrangThai"))) entity.TrangThai = reader.GetInt32(reader.GetOrdinal("TrangThai"));
                if (!reader.IsDBNull(reader.GetOrdinal("KieuThanhLy"))) entity.KieuThanhLy = reader.GetString(reader.GetOrdinal("KieuThanhLy"));
                collection.Add(entity);
            }
            return collection;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert methods.

        public long Insert()
        {
            return this.InsertTransaction(null);
        }

        //---------------------------------------------------------------------------------------------

        public long InsertTransaction(SqlTransaction transaction)
        {
            string spName = "p_SXXK_ThanhLy_LanThanhLy_Insert";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);
            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);
            this.db.AddInParameter(dbCommand, "@SoHoSo", SqlDbType.Int, this._SoHoSo);
            this.db.AddInParameter(dbCommand, "@NamThanhLy", SqlDbType.SmallInt, this._NamThanhLy);
            this.db.AddInParameter(dbCommand, "@NgayBatDau", SqlDbType.DateTime, this._NgayBatDau);
            this.db.AddInParameter(dbCommand, "@NgayKetThuc", SqlDbType.DateTime, this._NgayKetThuc);
            this.db.AddInParameter(dbCommand, "@SoQuyetDinh", SqlDbType.VarChar, this._SoQuyetDinh);
            this.db.AddInParameter(dbCommand, "@NgayQuyetDinh", SqlDbType.DateTime, this._NgayQuyetDinh);
            this.db.AddInParameter(dbCommand, "@TrangThai", SqlDbType.Int, this._TrangThai);
            this.db.AddInParameter(dbCommand, "@KieuThanhLy", SqlDbType.Char, this._KieuThanhLy);

            if (transaction != null)
            {
                return this.db.ExecuteNonQuery(dbCommand, transaction);
            }
            else
            {
                return this.db.ExecuteNonQuery(dbCommand);
            }
        }

        //---------------------------------------------------------------------------------------------

        public bool Insert(LanThanhLyCollection collection)
        {
            bool ret;
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    bool ret01 = true;
                    foreach (LanThanhLyBase item in collection)
                    {
                        if (item.InsertTransaction(transaction) <= 0)
                        {
                            ret01 = false;
                            break;
                        }
                    }
                    if (ret01)
                    {
                        transaction.Commit();
                        ret = true;
                    }
                    else
                    {
                        transaction.Rollback();
                        ret = false;
                    }
                }
                catch
                {
                    ret = false;
                    transaction.Rollback();
                }
                finally
                {
                    connection.Close();
                }
            }
            return ret;
        }

        //---------------------------------------------------------------------------------------------		

        public void InsertTransaction(SqlTransaction transaction, LanThanhLyCollection collection)
        {
            foreach (LanThanhLyBase item in collection)
            {
                item.InsertTransaction(transaction);
            }
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert / Update methods.
        public int InsertUpdate()
        {
            return this.InsertUpdateTransaction(null);
        }

        //---------------------------------------------------------------------------------------------

        public int InsertUpdateTransaction(SqlTransaction transaction)
        {
            string spName = "p_SXXK_ThanhLy_LanThanhLy_InsertUpdate";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);
            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);
            this.db.AddInParameter(dbCommand, "@SoHoSo", SqlDbType.Int, this._SoHoSo);
            this.db.AddInParameter(dbCommand, "@NamThanhLy", SqlDbType.SmallInt, this._NamThanhLy);
            this.db.AddInParameter(dbCommand, "@NgayBatDau", SqlDbType.DateTime, this._NgayBatDau);
            this.db.AddInParameter(dbCommand, "@NgayKetThuc", SqlDbType.DateTime, this._NgayKetThuc);
            this.db.AddInParameter(dbCommand, "@SoQuyetDinh", SqlDbType.VarChar, this._SoQuyetDinh);
            this.db.AddInParameter(dbCommand, "@NgayQuyetDinh", SqlDbType.DateTime, this._NgayQuyetDinh);
            this.db.AddInParameter(dbCommand, "@TrangThai", SqlDbType.Int, this._TrangThai);
            this.db.AddInParameter(dbCommand, "@KieuThanhLy", SqlDbType.Char, this._KieuThanhLy);

            if (transaction != null)
                return this.db.ExecuteNonQuery(dbCommand, transaction);
            else
                return this.db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public bool InsertUpdate(LanThanhLyCollection collection)
        {
            bool ret;
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    bool ret01 = true;
                    foreach (LanThanhLyBase item in collection)
                    {
                        if (item.InsertUpdateTransaction(transaction) <= 0)
                        {
                            ret01 = false;
                            break;
                        }
                    }
                    if (ret01)
                    {
                        transaction.Commit();
                        ret = true;
                    }
                    else
                    {
                        transaction.Rollback();
                        ret = false;
                    }
                }
                catch
                {
                    ret = false;
                    transaction.Rollback();
                }
                finally
                {
                    connection.Close();
                }
            }
            return ret;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Update methods.

        public int Update()
        {
            return this.UpdateTransaction(null);
        }

        //---------------------------------------------------------------------------------------------

        public int UpdateTransaction(SqlTransaction transaction)
        {
            string spName = "p_SXXK_ThanhLy_LanThanhLy_Update";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);
            this.db.AddInParameter(dbCommand, "@SoHoSo", SqlDbType.Int, this._SoHoSo);
            this.db.AddInParameter(dbCommand, "@NamThanhLy", SqlDbType.SmallInt, this._NamThanhLy);
            this.db.AddInParameter(dbCommand, "@NgayBatDau", SqlDbType.DateTime, this._NgayBatDau);
            this.db.AddInParameter(dbCommand, "@NgayKetThuc", SqlDbType.DateTime, this._NgayKetThuc);
            this.db.AddInParameter(dbCommand, "@SoQuyetDinh", SqlDbType.VarChar, this._SoQuyetDinh);
            this.db.AddInParameter(dbCommand, "@NgayQuyetDinh", SqlDbType.DateTime, this._NgayQuyetDinh);
            this.db.AddInParameter(dbCommand, "@TrangThai", SqlDbType.Int, this._TrangThai);
            this.db.AddInParameter(dbCommand, "@KieuThanhLy", SqlDbType.Char, this._KieuThanhLy);

            if (transaction != null)
                return this.db.ExecuteNonQuery(dbCommand, transaction);
            else
                return this.db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public void UpdateCollection(LanThanhLyCollection collection, SqlTransaction transaction)
        {
            foreach (LanThanhLyBase item in collection)
            {
                item.UpdateTransaction(transaction);
            }
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Delete methods.

        public int Delete()
        {
            return this.DeleteTransaction(null);
        }

        //---------------------------------------------------------------------------------------------

        public int DeleteTransaction(SqlTransaction transaction)
        {
            string spName = "p_SXXK_ThanhLy_LanThanhLy_Delete";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);

            if (transaction != null)
                return this.db.ExecuteNonQuery(dbCommand, transaction);
            else
                return this.db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public void DeleteCollection(LanThanhLyCollection collection, SqlTransaction transaction)
        {
            foreach (LanThanhLyBase item in collection)
            {
                item.DeleteTransaction(transaction);
            }
        }

        //---------------------------------------------------------------------------------------------

        public bool DeleteCollection(LanThanhLyCollection collection)
        {
            bool ret;
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    bool ret01 = true;
                    foreach (LanThanhLyBase item in collection)
                    {
                        if (item.DeleteTransaction(transaction) <= 0)
                        {
                            ret01 = false;
                            break;
                        }
                    }
                    if (ret01)
                    {
                        transaction.Commit();
                        ret = true;
                    }
                    else
                    {
                        transaction.Rollback();
                        ret = false;
                    }
                }
                catch
                {
                    ret = false;
                    transaction.Rollback();
                }
                finally
                {
                    connection.Close();
                }
            }
            return ret;
        }
        #endregion
        

        #region WS
        private void updateDatabase(DataSet ds, SqlTransaction transaction)
        {
            foreach(DataRow row in ds.Tables[0].Rows)
            {
                LanThanhLyBase lanTL=new LanThanhLyBase();
                lanTL.LanThanhLy=Convert.ToInt32(row["LAN_TL"].ToString());
                lanTL.MaDoanhNghiep=(row["MA_DV"].ToString());
                lanTL.MaHaiQuan=(row["MA_HQ"].ToString());
                lanTL.NamThanhLy=Convert.ToInt16(row["NAM_TL"].ToString());
                lanTL.NgayBatDau=Convert.ToDateTime(row["NGAY_BD_TL"].ToString());
                try
                {
                    lanTL.NgayKetThuc = Convert.ToDateTime(row["NGAY_KT_TL"].ToString());
                }
                catch { }
                try
                {
                    lanTL.NgayQuyetDinh = Convert.ToDateTime(row["NGAY_QDTL"].ToString());
                }
                catch { }
                try
                {
                    lanTL.SoQuyetDinh = (row["SO_QDTL"].ToString());
                }
                catch { }
                lanTL.SoHoSo=Convert.ToInt32(row["SO_HS_TL"].ToString());            
                lanTL.TrangThai=Convert.ToInt32(row["TRANG_THAI"].ToString());
                lanTL.InsertUpdateTransaction(transaction);
            }
        }

        public static void DongBoDuLieuHaiQuan(string mahaiquan, string madoanhnghiep)
        {
            Company.BLL.WS.SXXK.SXXKService sxxk = new Company.BLL.WS.SXXK.SXXKService();
            string[] dsHoSo = sxxk.ThanhKhoan_GetDanhSach(mahaiquan, madoanhnghiep);
            if (dsHoSo == null)
                throw new Exception("Không có hồ sơ thanh lý nào cả");
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    DataSet ds = new DataSet();
                    #region Cập nhật hồ sơ thanh lý
                    string stHoSoThanhLy = dsHoSo[0];
                    System.IO.StringReader xmlSR = new System.IO.StringReader(stHoSoThanhLy);
                    ds.ReadXmlSchema(xmlSR);
                    xmlSR = new System.IO.StringReader(dsHoSo[1]);   
                    ds.ReadXml(xmlSR);
                    LanThanhLyBase lanTL = new LanThanhLyBase();
                    lanTL.updateDatabase(ds, transaction);
                    #endregion

                    #region To khai nhap khau

                    string stDanhSachToKhainhap = dsHoSo[2];
                    xmlSR = new System.IO.StringReader(stDanhSachToKhainhap);
                    ds = new DataSet();
                    ds.ReadXmlSchema(xmlSR);
                    xmlSR = new System.IO.StringReader(dsHoSo[3]);
                    ds.ReadXml(xmlSR);                                        
                    ToKhaiNhap.updateDatabase(ds, transaction);

                    #endregion

                    #region To khai xuat khau

                    string stDanhSachToKhaixuat = dsHoSo[4];
                    xmlSR = new System.IO.StringReader(stDanhSachToKhaixuat);
                    ds = new DataSet();
                    ds.ReadXmlSchema(xmlSR);
                    xmlSR = new System.IO.StringReader(dsHoSo[5]);
                    ds.ReadXml(xmlSR);
                    ToKhaiXuat.updateDatabase(ds, transaction);
                    #endregion

                    #region NPL chua thanh ly

                    string stNPLChuaThanhLy = dsHoSo[6];
                    if (stNPLChuaThanhLy != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLChuaThanhLy);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[7]);
                        ds.ReadXml(xmlSR);
                        NPLChuaThanhLy.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL xin huy

                    string stNPLHuy = dsHoSo[8];
                    if (stNPLHuy != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLHuy);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[9]);
                        ds.ReadXml(xmlSR);
                        NPLHuy.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL nop thue

                    string stNPLNopthue = dsHoSo[10];
                    if (stNPLNopthue != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLNopthue);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[11]);
                        ds.ReadXml(xmlSR);
                        NPLNopThue.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL tai xuat

                    string stNPLTaiXuat = dsHoSo[12];
                    if (stNPLTaiXuat != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLTaiXuat);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[13]);
                        ds.ReadXml(xmlSR);
                        NPLTaiXuat.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL tam nop thue

                    string stNPLTamNopThue = dsHoSo[14];
                    if (stNPLTamNopThue != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLTamNopThue);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[15]);
                        ds.ReadXml(xmlSR);
                        NPLTamNopThue.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL xuat gia cong

                    string stNPLXuatGiaCong = dsHoSo[16];
                    if (stNPLXuatGiaCong != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLXuatGiaCong);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[17]);
                        ds.ReadXml(xmlSR);
                        NPLXGC.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL xuat kinh doanh

                    string stNPLXuatNKD = dsHoSo[18];
                    if (stNPLXuatNKD != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLXuatNKD);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[19]);
                        ds.ReadXml(xmlSR);
                        NPLXuatNKD.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL tu cung ung
                    string stNPLTucung = dsHoSo[20];
                    if (stNPLTucung != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLTucung);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[21]);
                        ds.ReadXml(xmlSR);
                        NPLTuCungUng.updateDatabase(ds, transaction);
                    }
                    #endregion
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        public static void DongBoDuLieuHaiQuan(string mahaiquan, string madoanhnghiep,int lanthanhly)
        {
            Company.BLL.WS.SXXK.SXXKService sxxk = new Company.BLL.WS.SXXK.SXXKService();
            string[] dsHoSo = sxxk.ThanhKhoan_GetDanhSachOfLanThanhLy(mahaiquan, madoanhnghiep,lanthanhly);
            if (dsHoSo == null)
                throw new Exception("Không có hồ sơ thanh lý nào cả");
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    DataSet ds = new DataSet();
                    #region Cập nhật hồ sơ thanh lý
                    string stHoSoThanhLy = dsHoSo[0];
                    System.IO.StringReader xmlSR = new System.IO.StringReader(stHoSoThanhLy);
                    ds.ReadXmlSchema(xmlSR);
                    xmlSR = new System.IO.StringReader(dsHoSo[1]);
                    ds.ReadXml(xmlSR);
                    LanThanhLyBase lanTL = new LanThanhLyBase();
                    lanTL.updateDatabase(ds, transaction);
                    #endregion

                    #region To khai nhap khau

                    string stDanhSachToKhainhap = dsHoSo[2];
                    xmlSR = new System.IO.StringReader(stDanhSachToKhainhap);
                    ds = new DataSet();
                    ds.ReadXmlSchema(xmlSR);
                    xmlSR = new System.IO.StringReader(dsHoSo[3]);
                    ds.ReadXml(xmlSR);
                    ToKhaiNhap.updateDatabase(ds, transaction);

                    #endregion

                    #region To khai xuat khau

                    string stDanhSachToKhaixuat = dsHoSo[4];
                    xmlSR = new System.IO.StringReader(stDanhSachToKhaixuat);
                    ds = new DataSet();
                    ds.ReadXmlSchema(xmlSR);
                    xmlSR = new System.IO.StringReader(dsHoSo[5]);
                    ds.ReadXml(xmlSR);
                    ToKhaiXuat.updateDatabase(ds, transaction);
                    #endregion

                    #region NPL chua thanh ly

                    string stNPLChuaThanhLy = dsHoSo[6];
                    if (stNPLChuaThanhLy != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLChuaThanhLy);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[7]);
                        ds.ReadXml(xmlSR);
                        NPLChuaThanhLy.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL xin huy

                    string stNPLHuy = dsHoSo[8];
                    if (stNPLHuy != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLHuy);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[9]);
                        ds.ReadXml(xmlSR);
                        NPLHuy.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL nop thue

                    string stNPLNopthue = dsHoSo[10];
                    if (stNPLNopthue != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLNopthue);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[11]);
                        ds.ReadXml(xmlSR);
                        NPLNopThue.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL tai xuat

                    string stNPLTaiXuat = dsHoSo[12];
                    if (stNPLTaiXuat != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLTaiXuat);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[13]);
                        ds.ReadXml(xmlSR);
                        NPLTaiXuat.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL tam nop thue

                    string stNPLTamNopThue = dsHoSo[14];
                    if (stNPLTamNopThue != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLTamNopThue);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[15]);
                        ds.ReadXml(xmlSR);
                        NPLTamNopThue.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL xuat gia cong

                    string stNPLXuatGiaCong = dsHoSo[16];
                    if (stNPLXuatGiaCong != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLXuatGiaCong);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[17]);
                        ds.ReadXml(xmlSR);
                        NPLXGC.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL xuat kinh doanh

                    string stNPLXuatNKD = dsHoSo[18];
                    if (stNPLXuatNKD != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLXuatNKD);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[19]);
                        ds.ReadXml(xmlSR);
                        NPLXuatNKD.updateDatabase(ds, transaction);
                    }
                    #endregion

                    #region NPL tu cung ung
                    string stNPLTucung = dsHoSo[20];
                    if (stNPLTucung != "")
                    {
                        xmlSR = new System.IO.StringReader(stNPLTucung);
                        ds = new DataSet();
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsHoSo[21]);
                        ds.ReadXml(xmlSR);
                        NPLTuCungUng.updateDatabase(ds, transaction);
                    }
                    #endregion
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        public static void GetNPLTonKho(string mahaiquan, string madoanhnghiep, int lanthanhly)
        {
            Company.BLL.WS.SXXK.SXXKService sxxk = new Company.BLL.WS.SXXK.SXXKService();
            string[] dsTonKho = sxxk.ThanhKhoan_GetDanhSachTonNPLOfLanThanhLy(mahaiquan, madoanhnghiep, lanthanhly);
            if (dsTonKho == null)
                throw new Exception("Không có  dữ liệu tồn kho nào cả");
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    DataSet ds = new DataSet();
                    //thong tin ton npl huy nhap???

                    System.IO.StringReader xmlSR = null;
                    //if (dsTonKho[0] != "")
                    //{
                    //    xmlSR = new System.IO.StringReader(dsTonKho[1]);
                    //    ds.ReadXmlSchema(xmlSR);
                    //    xmlSR = new System.IO.StringReader(dsTonKho[0]);
                    //    ds.ReadXmlSchema(xmlSR);
                    //    TonNPLNhap.Updatebase(ds, transaction);
                    //}

                    ////thong tin ton npl tai xuat huy???
                    //if (dsTonKho[2] != "")
                    //{
                    //    ds = new DataSet();
                    //    xmlSR = new System.IO.StringReader(dsTonKho[3]);
                    //    ds.ReadXmlSchema(xmlSR);
                    //    xmlSR = new System.IO.StringReader(dsTonKho[2]);
                    //    ds.ReadXml(xmlSR);
                    //    TonNPLTaiXuat.Updatebase(ds, transaction);
                    //}
                    ////thong tin ton npl xuat huy???
                    //if (dsTonKho[4] != "")
                    //{
                    //   ds = new DataSet();
                    //    xmlSR = new System.IO.StringReader(dsTonKho[5]);
                    //    ds.ReadXmlSchema(xmlSR);
                    //    xmlSR = new System.IO.StringReader(dsTonKho[4]);
                    //    ds.ReadXml(xmlSR);
                    //    TonNPLXuat.Updatebase(ds, transaction);
                    //}
                    //thong tin npl tai xuat tl ton
                    if (dsTonKho[0] != "")
                    {
                        ds = new DataSet();
                        xmlSR = new System.IO.StringReader(dsTonKho[1]);
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsTonKho[0]);
                        ds.ReadXml(xmlSR);
                        {                            
                            NPLTaiXuatTon.Updatebase(ds, transaction);
                            NPLTaiXuatTon_TL.Updatebase(ds, transaction);
                        }
                    }

                    //thong tin npl xuat tl ton
                    if (dsTonKho[2] != "")
                    {
                         ds = new DataSet();
                        xmlSR = new System.IO.StringReader(dsTonKho[3]);
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsTonKho[2]);
                        ds.ReadXml(xmlSR);
                        {                            
                            NPLXuatTon.Updatebase(ds, transaction);
                            NPLXuatTon_TL.Updatebase(ds, transaction);
                        }
                    }
                    //thong tin npl nhap tl ton
                    if (dsTonKho[4] != "")
                    {
                        //thanh khoan luong ton
                        ds = new DataSet();
                        xmlSR = new System.IO.StringReader(dsTonKho[5]);
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsTonKho[4]);
                        ds.ReadXml(xmlSR);
                        NPLNhapTon.Updatebase(ds, transaction);
                        NPLNhapTon_TL.Updatebase(ds, transaction);
                        ds = new DataSet();
                        xmlSR = new System.IO.StringReader(dsTonKho[7]);
                        ds.ReadXmlSchema(xmlSR);
                        xmlSR = new System.IO.StringReader(dsTonKho[6]);
                        ds.ReadXml(xmlSR);
                        NPLNhapTon_CT.Updatebase(ds, transaction);
                        NPLNhapTon_TH.Updatebase(ds, transaction);                       
                    }                    
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        
        public static int getLanThanhLyMoiNhat(string maDoanhNghiep,string maHaiQuan)
        {
            string sql = "SELECT ISNUll(max(LanThanhLy),0) + 1 from t_SXXK_ThanhLy_LanThanhLy WHERE MaDoanhNghiep ='"+ maDoanhNghiep +"' AND maHaiQuan='"+ maHaiQuan+"'";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbc = db.GetSqlStringCommand(sql);
            try
            {
                return (int)db.ExecuteScalar(dbc);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}